home *** CD-ROM | disk | FTP | other *** search
/ AmigActive 24 / AACD 24.iso / AACD / Information / WebSites / AmigaNorthThames / cgi-bin / ANTS / readcsvplus.pl < prev    next >
Perl Script  |  2001-04-21  |  23KB  |  559 lines
  1. #!/usr/bin/perl
  2. print "Content-type: text/html"."\n\n";
  3.  
  4. ######################################
  5. #                                    #
  6. # ReadCSVPlus v1.3                   #
  7. # Copyright 2000 by Mutasem Abudahab #
  8. # mutasem@abudahab.com               #
  9. # http://ezscripting.com             #
  10. #                                    #
  11. # Last modified on Aug. 28, 2000     #
  12. #                                    #
  13. ######################################
  14. #
  15. # Copyright Notice:
  16. # Copyright 2000 Mutasem R. Abudahab.  All Rights Reserved.
  17. #
  18. # This code may be used and modified by anyone so long as this header and
  19. # copyright information remains intact.  By using this code you agree to 
  20. # indemnify Mutasem R. Abudahab from any liability that might arise from its 
  21. # use.  You must obtain written consent before selling or redistributing 
  22. # this code.
  23.  
  24. #------------------- Start working on the script --------------------#
  25.  
  26. # Replace "data.csv" with the full path to your csv database file
  27.  
  28.     $csv_file_path="antsdata.csv";
  29.  
  30. # Replace "template.html" with the full path to your HTML template.
  31.  
  32.     $html_template_path="ants_temp.html";
  33.  
  34. # Replace "http://www.domain.com/images/new.gif" with the URL to the graphic
  35. # image that will be displayed next to a record, when ReadCSVPlus marks it as
  36. # new.
  37.  
  38.     $new_image_file="http://www.domain.com/images/new.gif";
  39.  
  40. # Replace this value with the number of days related to <<date_local>> after
  41. # which the script will stop displaying the 'new' image graphic.
  42.  
  43.     $new_compare_dat="10";
  44.  
  45. # Replace this value with the number of random records the script will display
  46. # when you query for a random display (display=random).
  47.  
  48.     $num_random=1;
  49.  
  50. # Replace this value with the message the script will display, when it finds
  51. # no matches to a query
  52.  
  53.     $no_matches_found="sorry, no results found";
  54.  
  55. #-------------- DO NOT CHANGE ANYTHING UNDER THIS LINE --------------#
  56.  
  57. $nv=0;
  58. $qs=$ENV{'QUERY_STRING'};
  59. if($sort_field=&pick_up("sort_a",$qs)){
  60.         $sort_a="yes";
  61.         }
  62. if($s_field=&pick_up("sort_d",$qs)){
  63.         $sort_d="yes";
  64.         }
  65. if($sort_d eq "yes"){
  66.         $sort_field=$s_field;
  67.         }
  68. @csv_lines=&modify_CSV($csv_file_path);
  69. $lcl=@csv_lines;
  70. @headers=split(";",$csv_lines[0]);
  71. foreach $one (@headers){
  72.         $one=&search_prepare($one);
  73.         }
  74. $l_headers=@headers;
  75. for($l=0;$l<=($l_headers-1);$l=$l+1){
  76.         if($headers[$l]=~/^$sort_field$/){
  77.                 $sort_location=$l+1;
  78.                 $s_flag=999;
  79.                 }
  80.         if(($headers[$l]=~/^date_local$/)||($headers[$l]=~/^date_local$/)){
  81.                 $date_location=$l+1;
  82.                 }
  83.         }
  84. if(($s_flag !=999)&&($sort_field=~/\W/i)){
  85.         &produce_error(
  86.                 "The field name that you are trying to sort by can not be found in your CSV database file."
  87.                 );
  88.         }
  89. if($rec_range=&pick_up("rec_range",$qs)){
  90.         if($rec_range=~/^\((\d+)\)\((\d+)-(\d+)\)(.+)/){
  91.                 $page=$1;
  92.                 $rec_start=$2;
  93.                 $rec_end=$3;
  94.                 $recs=$4;
  95.                 @matched_lines=split(" ",$recs);
  96.                 foreach $match (@matched_lines){
  97.                         if($match=~/\D/){
  98.                                 &produce_error(
  99.                                         "unpropper form found in rec_range in URL."
  100.                                         );
  101.                                 }
  102.                         }
  103.                 goto PARTIAL;
  104.                 }
  105.                 else{
  106.                         &produce_error(
  107.                                 "unpropper form found in rec_range in URL."
  108.                                 );
  109.                         }
  110.         }
  111. if($display=&pick_up("display",$qs)){
  112.         if($display eq "random"){
  113.                 $display="random";
  114.                 goto PREPARE;
  115.                 }
  116.         elsif($display eq "all"){
  117.                 $display="all";
  118.                 goto PREPARE;
  119.                 }
  120.                 else{
  121.                         &produce_error(
  122.                                 "undefined display mode requested."
  123.                                 );
  124.                         }
  125.         }
  126. @conditions=split("&",$qs);
  127. $l_conditions=@conditions;
  128. $absolute=$l_conditions;
  129. foreach $one (@conditions){
  130.         $one=~s/%([\dA-Fa-f][\dA-Fa-f])/pack("C",hex($1))/eg;
  131.         $one=~tr/+/ /;
  132.         }
  133. for($l=1;$l<=(@csv_lines-1);$l=$l+1){
  134.         @line_entries=split(";",$csv_lines[$l]);
  135.         foreach $entry (@line_entries){
  136.                 $entry=&search_prepare($entry);
  137.                 $entry=&sweep_spaces($entry);
  138.                 }
  139.         CHECK_CONDITIONS:
  140.         foreach $rule (@conditions){
  141.                 if($rule=~/[^=<>!]<[^=<>!]/){
  142.                         ($var,$val)=split("<",$rule);
  143.                         &error_if_nonnumiric;
  144.                         $var=&sweep_spaces($var);
  145.                         $val=&sweep_spaces($val);
  146.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  147.                                 if($var=~/^$headers[$p]$/){
  148.                                         if($line_entries[$p] < $val){
  149.                                                 $c_match=$c_match+1;
  150.                                                 next CHECK_CONDITIONS;
  151.                                                 }
  152.                                         }
  153.                                 }
  154.                         }
  155.                 elsif($rule=~/[^=<>!]>[^=<>!]/){
  156.                         ($var,$val)=split(">",$rule);
  157.                         &error_if_nonnumiric;
  158.                         $var=&sweep_spaces($var);
  159.                         $val=&sweep_spaces($val);
  160.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  161.                                 if($var=~/^$headers[$p]$/){
  162.                                         if($line_entries[$p] > $val){
  163.                                                 $c_match=$c_match+1;
  164.                                                 next CHECK_CONDITIONS;
  165.                                                 }
  166.                                         }
  167.                                 }
  168.                         }
  169.                 elsif($rule=~/[^=<>!]>[^=<>!]/){
  170.                         ($var,$val)=split(">",$rule);
  171.                         &error_if_nonnumiric;
  172.                         $var=&sweep_spaces($var);
  173.                         $val=&sweep_spaces($val);
  174.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  175.                                 if($var=~/^$headers[$p]$/){
  176.                                         if($line_entries[$p] > $val){
  177.                                                 print $line_entries[$p]."<BR>";
  178.                                                 $c_match=$c_match+1;
  179.                                                 next CHECK_CONDITIONS;
  180.                                                 }
  181.                                         }
  182.                                 }
  183.                         }
  184.                 elsif(($rule=~/[^=<>!](>=)[^=<>!]/)||($rule=~/[^=<>!](=>)[^=<>!]/)){
  185.                         ($var,$val)=split($+,$rule);
  186.                         &error_if_nonnumiric;
  187.                         $var=&sweep_spaces($var);
  188.                         $val=&sweep_spaces($val);
  189.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  190.                                 if($var=~/^$headers[$p]$/){
  191.                                         if($line_entries[$p] >= $val){
  192.                                                 print $line_entries[$p]."<BR>";
  193.                                                 $c_match=$c_match+1;
  194.                                                 next CHECK_CONDITIONS;
  195.                                                 }
  196.                                         }
  197.                                 }
  198.                         }
  199.                 elsif(($rule=~/[^=<>!](<=)[^=<>!]/)||($rule=~/[^=<>!](=<)[^=<>!]/)){
  200.                         ($var,$val)=split($+,$rule);
  201.                         &error_if_nonnumiric;
  202.                         $var=&sweep_spaces($var);
  203.                         $val=&sweep_spaces($val);
  204.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  205.                                 if($var=~/^$headers[$p]$/){
  206.                                         if($line_entries[$p] <= $val){
  207.                                                 print $line_entries[$p]."<BR>";
  208.                                                 $c_match=$c_match+1;
  209.                                                 next CHECK_CONDITIONS;
  210.                                                 }
  211.                                         }
  212.                                 }
  213.                         }
  214.                 elsif($rule=~/[^=<>!]!![^=<>!]/){
  215.                         ($var,$val)=split("!!",$rule);
  216.                         &error_if_nonnumiric;
  217.                         $var=&sweep_spaces($var);
  218.                         $val=&sweep_spaces($val);
  219.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  220.                                 if($var=~/^$headers[$p]$/){
  221.                                         if($line_entries[$p] != $val){
  222.                                                 $c_match=$c_match+1;
  223.                                                 next CHECK_CONDITIONS;
  224.                                                 }
  225.                                         }
  226.                                 }
  227.                         }
  228.                elsif(($rule=~/[^=<>!](!=)[^=<>!]/)||($rule=~/[^=<>!](=!)[^=<>!]/)){
  229.                         ($var,$val)=split($+,$rule);
  230.                         $var=&sweep_spaces($var);
  231.                         $val=&sweep_spaces($val);
  232.                         if($val=~/^".+"$/){
  233.                         $val=~s/^"//;
  234.                         $val=~s/"$//;
  235.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  236.                                 if($var=~/^$headers[$p]$/){
  237.                                         if($line_entries[$p] !~ /^$val$/i){
  238.                                                 $c_match=$c_match+1;
  239.                                                 next CHECK_CONDITIONS;
  240.                                                 }
  241.                                         }
  242.                                 }
  243.                                 } #end of the new if exact
  244.                         else{
  245.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  246.                                 if($var=~/^$headers[$p]$/){
  247.                                         if($line_entries[$p] !~ /$val/i){
  248.                                                 $c_match=$c_match+1;
  249.                                                 next CHECK_CONDITIONS;
  250.                                                 }
  251.                                         }
  252.                                 }
  253.  
  254.                                 }
  255.                         }
  256.                elsif(($rule=~/[^=<>!]=[^=<>!]/)||($rule=~/[^=<>!]=$/)){
  257.                         ($var,$val)=split("=",$rule);
  258.                         if($val !~/\S/){
  259.                                 $c_match=$c_match+1;
  260.                                 next CHECK_CONDITIONS;
  261.                                 }
  262.                         $var=&sweep_spaces($var);
  263.                         $val=&sweep_spaces($val);
  264.                         if($val=~/^".+"$/){
  265.                                 $val=~s/^"//;
  266.                                 $val=~s/"$//;
  267.                                 for($p=0;$p<=($l_headers-1);$p=$p+1){
  268.                                         if($var=~/^$headers[$p]$/){
  269.                                                 if($line_entries[$p] =~ /^$val$/i){
  270.                                                         $c_match=$c_match+1;
  271.                                                         next CHECK_CONDITIONS;
  272.                                                         }
  273.                                                 }
  274.                                 }
  275.  
  276.                                 }
  277.                         elsif($var=~/^search$/i){
  278.                                 foreach $lin_ent (@line_entries){
  279.                                         if($lin_ent=~/$val/i){
  280.                                                 $c_match=$c_match+1;
  281.                                                 next CHECK_CONDITIONS;
  282.                                                 }
  283.                                         }
  284.                                 }
  285.                                 else{
  286.                         for($p=0;$p<=($l_headers-1);$p=$p+1){
  287.                                 if($var=~/^$headers[$p]$/){
  288.                                         if($line_entries[$p] =~ /$val/i){
  289.                                                 $c_match=$c_match+1;
  290.                                                 next CHECK_CONDITIONS;
  291.                                                 }
  292.                                         }
  293.                                 }
  294.                                 }
  295.                         }
  296.                else{
  297.                         &produce_error(
  298.                                 "Unreadable request found in your query.",
  299.                                 "Please check the syntax of your query and try again."
  300.                                 );
  301.                         }
  302.                 }
  303.         if($c_match == $absolute){
  304.                 $matched_lines[$num]=$l;
  305.                 if($sort_location != 0){
  306.                         $sort_values[$num]=$line_entries[$sort_location-1];
  307.                         }
  308.                 if($date_location != 0){
  309.                         $date_values[$num]=$line_entries[$date_location-1];
  310.                         $date_values[$num]=&sweep_spaces($date_values[$num]);
  311.                         if($date_values[$num]!~/\d{1,2}[,-\/\\]\d{1,2}[,-\/\\]\d{4}/){
  312.                                 $date_values[$num]="not dated";
  313.                                 }
  314.                         }
  315.                 $num=$num+1;
  316.                 }
  317.         $c_match=0;
  318.         }
  319. PREPARE:
  320. $lcsv=@csv_lines-1;
  321. if($display eq "all"){
  322.         @matched_lines=(1..$lcsv);
  323.         }
  324. if($display eq "random"){
  325.         srand;
  326.         if($num_random < 1){
  327.                 &produce_error(
  328.                         "cannot display less than 1 random records. Set $num_random to a value greater than 1." 
  329.                         );
  330.                 }
  331.         if($num_random > $lcsv){
  332.                 $num_random=$lcsv;
  333.                 }
  334.         @matched_lines=();
  335.         $num=0;
  336.         $l=1;
  337.         REDO:
  338.         for($l;$l<=$num_random;$l=$l+1){
  339.                 $record_n=int(rand($lcsv))+1;
  340.                 $matched_lines[$num]=$record_n;
  341.                 for($u=0;$u<=($num-1);$u=$u+1){
  342.                         if($matched_lines[$num] == $matched_lines[$u]){
  343.                                 goto REDO;
  344.                                 }
  345.                         }
  346.                 $num=$num+1;
  347.                 }
  348.         }
  349. $lml=@matched_lines;
  350. if(($sort_a eq "yes")||($sort_d eq "yes")){
  351.         &sort_results;
  352.         }
  353. SHOW:
  354. if(open(HTML,$html_template_path)){
  355.         @html_lines=<HTML>;
  356.         close(HTML);
  357.         $html=join("",@html_lines);
  358.         unless($html=~/(.*)<template>(.*)<\/template>(.*)/is){
  359.                 &produce_error(
  360.                         "Your HTML template file does not contain template tags necessary to display records."
  361.                         );
  362.                 }
  363.         $upper=$1;
  364.         $html_temp=$2;
  365.         $footer=$3;
  366.         foreach $part ($upper,$html_temp,$footer){
  367.                 $part=&translate_special_variables($part,($lml-$nv),($lcl-1));
  368.                 }
  369.         if(($lml-$nv) == 0){
  370.                 print $upper;
  371.                 print $no_matches_found."<BR>\n";
  372.                 print $footer;
  373.                 exit(0);
  374.                 }
  375.         print $upper."\n";
  376.         for($w=0;$w<=(@matched_lines-1);$w=$w+1){
  377.                 @m_e=split(";",$csv_lines[$matched_lines[$w]]);
  378.                 $out=$html_temp;
  379.                 foreach $m_entry (@m_e){
  380.                         $m_entry=&search_prepare($m_entry);
  381.                         }
  382.                 for($lh=0;$lh<=($l_headers-1);$lh=$lh+1){
  383.                         $out=~s/!!$headers[$lh]!!/$m_e[$lh]/isg;
  384.                         }
  385.                 if($out=~/!!NEW_IMAGE!!/i){
  386.                         $new_img=&check_if_new($new_compare_dat,$new_image_file,$date_values[$w]);
  387.                         $out=~s/!!NEW_IMAGE!!/$new_img/isg;
  388.                         }
  389.                 $out=~s/!!\#_LINE!!/$csv_file_path&line_$matched_lines[$w]/ig;
  390.                 $out=~s/!!.*!!//g;
  391.                 print $out;
  392.                 }
  393.         print $footer."\n";
  394.         }
  395.         else{
  396.                 &produce_error(
  397.                         "could not open HTML template file. Please check that you have provided",
  398.                         "the script with the correct path in \$html_template_path variable."
  399.                         );
  400.                 }
  401. exit;
  402. sub modify_CSV
  403. {
  404. if(open(CSV,$_[0])){
  405.     }
  406.     else{
  407.                 &produce_error(
  408.                         "Can't open CSV database file.",
  409.                         "Please, check that you have provided the cgi script with correct path to your CSV database file.",
  410.                         ); 
  411.     }
  412. $ccc=0;
  413. while($in_lin=<CSV>){
  414.         if($in_lin!~/\S/){
  415.                 next;
  416.                 }
  417.         $lines[$ccc]=$in_lin;
  418.         $ccc=$ccc+1;
  419.         }
  420. $lines_length=@lines;
  421. $lines_length=$lines_length-1;
  422. for($l=0;$l<=$lines_length;$l=$l+1){
  423.         $lines[$l]=~s/""/%01/g;
  424.         while($lines[$l]=~/("[^"]+")/){
  425.                 $match=$1;
  426.                 $match=~s/"//g;
  427.                 $match=~s/,/%02/g;
  428.                 $lines[$l]=~s/("[^"]+")/$match/;
  429.                 }
  430.         }
  431. close(CSV);
  432. chomp(@lines);
  433. return @lines;
  434. }  
  435. sub search_prepare{
  436.         $_[0]=~s/%01/"/g;
  437.         $_[0]=~s/%02/,/g;
  438.         return $_[0];
  439.         }
  440. sub produce_error
  441. {
  442. print "<HTML><HEAD><TITLE>Error message</TITLE><BODY>\n";
  443. print "<img border=\"0\" src=\"http://www.ezscripting.com/images/title01.gif\"><HR>";
  444. print "<CENTER><IFRAME SRC=\"http://www.ezscripting.com/banners/ads.pl?iframe\" MARGINWIDTH=0 MARGINHEIGHT=0 HSPACE=0 VSPACE=0 FRAMEBORDER=0 SCROLLING=NO WIDTH=468 HEIGHT=60></IFRAME></CENTER>";
  445. print "<BR><font face=\"Arial\"><B>ERROR MESSAGE:</B></FONT><BR><BR>";
  446. print "<font face=\"Arial\">";
  447. foreach $line (@_){
  448.         print "$line<BR>\n";
  449.         }
  450.  
  451. print "<BR>For more information go to <a href=\"http://www.ezscripting.com\">WWW.EZSCRIPTING.COM</a>";
  452. print "</FONT>";
  453. print "<p align=\"center\"><font size=\"1\" face=\"Arial\"><a href=\"http://www.ezscripting.com/admin/add_bug.shtml\">Report Bug</a> - <a href=\"http://www.ezscripting.com/faq.shtml\">FAQ</a> - <a href=\"http://www.ezscripting.com/forums.shtml\">Discussion Forums</a></font></p><hr>";
  454. print "<p align=\"right\"><b><font size=\"2\" face=\"Arial\">ReadCSV<font color=\"#FF0000\">Plus </font></font></b><font face=\"Arial\" size=\"1\">by <a href=\"mailto:mutasem\@abudahab.com\">Mutasem Abudahab</a>, <a href=\"http://www.ezscripting.com\">EZScripting.com</a></font></p>";
  455. print "</BODY></HTML>";
  456. exit(0);
  457. return (1);
  458. }
  459. sub error_if_nonnumiric
  460. {
  461. if($val =~ /\D/){
  462.         &produce_error(
  463.                 "You cannot use non-numiric values as right arguments of:",
  464.                 "1- Greater than.",
  465.                 "2- Less than.",
  466.                 "3- Equal or greater than.",
  467.                 "4- Equal or less than.",
  468.                 "Operators."
  469.                 );
  470.         }
  471. return (1);
  472. }
  473. sub sweep_spaces{
  474.         $_[0]=~s/^ +//;
  475.         $_[0]=~s/ +$//;
  476.         return $_[0];
  477.         }
  478. sub translate_special_variables{
  479.         ($secs,$mins,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=gmtime(time);
  480.         $b_time="$secs".":"."$mins".":"."$hour"." GMT";
  481.         $b_date="$mday".",".($mon+1).",".(1900+$year);
  482.         $_[0]=~s/!!#_matches!!/$_[1]/isg;
  483.         $_[0]=~s/!!#_total!!/$_[2]/isg;
  484.         $_[0]=~s/!!#_date!!/$b_date/isg;
  485.         $_[0]=~s/!!#_time!!/$b_time/isg;
  486.         return $_[0];
  487.         }
  488. sub pick_up{
  489.         if(($_[1]=~/^$_[0]=([^&\b]+)/i)||($_[1]=~/&$_[0]=([^&\b]+)/i)){
  490.                 $pick=$+;
  491.                 $pick=~s/%([\dA-Fa-f][\dA-Fa-f])/pack("C",hex($1))/eg;
  492.                 $pick=~tr/+/ /;
  493.                 $_[1]=~s/$_[0]=[^&\b]+//i;
  494.                 $_[1]=~s/&&/&/g;
  495.                 $_[1]=~s/^&//g;
  496.                 $_[1]=~s/&$//g;
  497.                 }
  498.                 else{
  499.                         return 0;
  500.                         }
  501.         return $pick;
  502.         }
  503. sub check_if_new{
  504.         if($_[0] < 1){
  505.                 &produce_error(
  506.                         "You need to provide \$new_compare_dat with a value",
  507.                         "in order to display an image presenting new records."
  508.                         );
  509.                 }
  510.         if($_[1] eq ""){
  511.                 &produce_error(
  512.                         "you need to provide \$new_image_file with a path to an image file",
  513.                         "in order to display an image presenting new records."
  514.                         );
  515.                 }
  516.         if($_[2] eq "not dated"){
  517.                 return "!!NEW_IMAGE!!";
  518.                 }
  519.         ($dd,$mm,$yy)=split(",",$_[2]);
  520.         $days=$dd+($mm*30)+($yy*364);
  521.         ($secs,$mins,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=gmtime(time);
  522.         $ndays=$mday+(($mon+1)*30)+((1900+$year)*364);
  523.         $def=$ndays-$days;
  524.         if($def <= $new_compare_dat){        
  525.                 return "<IMG SRC=\"$_[1]\" ALT=\"new record\">";
  526.                 }
  527.         return "!!NEW_IMAGE!!";
  528.         }
  529. sub sort_results{
  530.         local $l;
  531.         for($l=0;$l<=(@matched_lines-1);$l=$l+1){
  532.                 $new_len=length $matched_lines[$l];
  533.                 if($new_len>=$dig_len){
  534.                         $dig_len=$new_len;
  535.                         }
  536.                                               
  537.                 }
  538.         for($l=0;$l<=(@matched_lines-1);$l=$l+1){
  539.                 $new_m_val=$dig_len-(length $matched_lines[$l]);
  540.                 $sort_values[$l]=$sort_values[$l]."|||||".("0"x$new_m_val).$matched_lines[$l];
  541.                 }
  542.         @sort_values=sort(@sort_values);
  543.         foreach $s_value (@sort_values){
  544.                 ($dummy,$matched_lines[$ghg])=split(/\|\|\|\|\|/,$s_value);
  545.                 $matched_lines[$ghg]=~s/^0//;
  546.                 $ghg=$ghg+1;
  547.                 }
  548.         if($sort_d eq "yes"){
  549.                 @matched_lines=reverse(@matched_lines);
  550.                 }
  551.         }
  552. sub error_if_meta{
  553.         if(($var=~/\/\$\^<>\|\\\[\]\(\)\{\}&=\?/)||($val=~/\/\?\$\^<>\|\\\[\]\(\)\{\}&=\?/)){
  554.                 &produce_error(
  555.                         "\?\$\^<>\|\\\[\]\(\)\{\}&=/ are meta charechters forbidden and reserved by servers for security purposes."
  556.                         );
  557.                 }
  558.         }
  559.